package com.erp.order.purchasing.dao.impl;

import com.erp.hr.base.dao.impl.BaseDaoImpl;
import com.erp.hr.base.util.CommonUtils;
import com.erp.order.purchasing.dao.PurchasingRequisitionDetailDao;
import com.erp.order.purchasing.model.*;
import com.erp.order.supply.model.SupplyMaterialDetailR;
import org.apache.commons.lang.StringUtils;
import org.redragon.framework.annotation.Permissions;
import org.redragon.framework.annotation.Permissions.PermissionType;
import org.redragon.framework.hibernate.DaoSupport;
import org.redragon.framework.hibernate.model.Pages;
import org.redragon.framework.util.DaoCRUDUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 采购申请详情 持久实现层
 * @Author: syz
 * @blame syz
 * @CreateTime: 2021/4/6
 */
@Repository
public class PurchasingRequisitionDetailDaoImpl extends BaseDaoImpl<PurchasingRequisitionDetail> implements PurchasingRequisitionDetailDao {

    /**
     * dao支持
     */
    @Autowired
    private DaoSupport daoSupport;


    /**
     * 获取列表分页数据
     * @param s 数据验证sql
     * @param pages 分页对象
     * @param paramObj 查询条件对象
     * @return 列表分页数据
     */
    @Override
    @Permissions(PermissionType.DATA_AUTH)
    public List<PurchasingRequisitionDetail> getDataObjectsForDataAuth(String s, Pages pages, PurchasingRequisitionDetail paramObj) {
        String sql =
                "SELECT " +
                    "prd.* " +
                "FROM " +
                    "purchasing_requisition_detail prd " +
                "LEFT JOIN " +
                    "purchasing_requisition pr " +
                "ON " +
                    "prd.requisition_id = pr.id " +
                "LEFT JOIN " +
                    "purchasing_audit pa " +
                "ON " +
                    "pr.purchasing_id = pa.id " +
                "WHERE " +
                    "pa.status = :pstatus " +
                "AND " +
                    "pa.buyer = :buyer " +
                "AND " +
                    "prd.inquiry_list_material_id IS NULL ";
        Map<String, Object> args = new HashMap<>();
        args.put("pstatus", CommonUtils.PURCHASING_REQUISITION_STATUS_AUDIT_END);
        args.put("buyer", paramObj.getBuyer());
        List<Long> notIn = paramObj.getNotIn();
        if (notIn != null && !notIn.isEmpty()) {
            sql += "AND " +
                        "prd.id NOT IN (:ids) ";
            args.put("ids", notIn);
        }
        if (StringUtils.isNotBlank(paramObj.getCompanyName())) {
            paramObj.setCompanyName("%" + paramObj.getCompanyName() + "%");
            args.put("companyname", paramObj.getCompanyName());
            sql += "AND " +
                        "pr.company_name LIKE :companyname ";
        }
        if (StringUtils.isNotBlank(paramObj.getDepartmentName())) {
            paramObj.setDepartmentName("%" + paramObj.getDepartmentName() + "%");
            sql += DaoCRUDUtil.getSQLLikeCondition(paramObj, "departmentName", "and pr.", args);
        }
        if (StringUtils.isNotBlank(paramObj.getMaterialBrand())) {
            paramObj.setMaterialBrand("%" + paramObj.getMaterialBrand() + "%");
            args.put("materialbrand", paramObj.getMaterialBrand());
            sql += "AND " +
                    "prd.material_brand LIKE :materialbrand ";
        }
        if (StringUtils.isNotBlank(paramObj.getMaterialName())) {
            paramObj.setMaterialName("%" + paramObj.getMaterialName() + "%");
            args.put("materialname", paramObj.getMaterialName());
            sql += "AND " +
                    "prd.material_name LIKE :materialname ";
        }
        if (StringUtils.isNotBlank(paramObj.getMaterialModel())) {
            paramObj.setMaterialModel("%" + paramObj.getMaterialModel() + "%");
            args.put("materialmodel", paramObj.getMaterialModel());
            sql += "AND " +
                    "prd.material_model LIKE :materialmodel ";
        }
        sql +=
                "AND " +
                    "prd.is_del = '0' " +
                "ORDER BY " +
                    "prd.material_category, " +
                    "prd.material_code, " +
                    "prd.material_name, " +
                    "prd.material_model, " +
                    "prd.material_brand";
        daoSupport.validateDataObject(paramObj);
        return daoSupport.getDataSqlByPage(
                sql,
                new HashMap<String, Class<?>>(){{
                    put("prd", PurchasingRequisitionDetail.class);
                }},
                args,
                pages);
    }

    /**
     * 根据id查询对象
     * @param id 对象id
     * @return 查询结果
     */
    @Override
    public PurchasingRequisitionDetail getDataObject(String id) {
        String sql =
                "SELECT " +
                    "prd.* " +
                "FROM " +
                    "purchasing_requisition_detail prd " +
                "WHERE " +
                    "prd.id = :id " +
                "AND " +
                    "prd.is_del = :isDel";
        Map<String, Object> args = new HashMap<>();
        args.put("id", id);
        args.put("isDel", CommonUtils.IS_DEL_NO);
        Map<String, Class<?>> entity = new HashMap<>();
        entity.put("prd", PurchasingRequisitionDetail.class);
        List<PurchasingRequisitionDetail> list = daoSupport.selectDataSql(sql, entity, args);
        return list != null && list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 根据条件查询对象集合
     * @param purchasingRequisitionDetail 申请详情对象
     * @return 对象集合
     */
    @Override
    public List<PurchasingRequisitionDetail> getDataObjects(PurchasingRequisitionDetail purchasingRequisitionDetail) {
        return null;
    }

    /**
     * 根据申请id获取详情集合
     * @param id 申请id
     * @return 详情集合
     */
    @Override
    public List<PurchasingRequisitionDetail> getListByRequisitionId(Long id) {
        return daoSupport.selectDataSql(
                "SELECT " +
                        "prd.* " +
                    "FROM " +
                        "purchasing_requisition_detail prd " +
                    "WHERE " +
                        "prd.requisition_id = :id " +
                    "AND " +
                        "prd.is_del = :isDel",
                new HashMap<String, Class<?>>(){{
                    put("prd", PurchasingRequisitionDetail.class);
                }},
                new HashMap<String, Object>(){{
                    put("id", id);
                    put("isDel", CommonUtils.IS_DEL_NO);
                }});
    }

    /**
     * 获取单一采购订单详情
     * @param inquiryListId 询价单id
     * @return 采购订单详情
     */
    @Override
    public List<PurchaseOrderDetailVO> getOnlyOrderDetail(Long inquiryListId) {
        return daoSupport.selectDataSql(
                "SELECT " +
                    "prd.id, " +
                    "ils.supplier_id supplierId, " +
                    "pr.company_name companyName, " +
                    "pr.create_user_name linkman, " +
                    "pr.tel tel, " +
                    "pr.address, " +
                    "prd.material_id materialId, " +
                    "prd.material_code materialCode, " +
                    "prd.material_brand materialBrand, " +
                    "prd.material_name materialName, " +
                    "prd.material_model materialModel, " +
                    "prd.material_category materialCategory, " +
                    "prd.quantity, " +
                    "prd.material_unit materialUnit, " +
                    "'0' price " +
                "FROM " +
                    "purchasing_requisition_detail prd " +
                "LEFT JOIN " +
                    "inquiry_list_material ilm " +
                "ON " +
                    "prd.inquiry_list_material_id = ilm.id " +
                "LEFT JOIN " +
                    "inquiry_list_supplier ils " +
                "ON " +
                    "ilm.inquiry_list_id = ils.inquiry_list_id " +
                "LEFT JOIN " +
                    "purchasing_requisition pr " +
                "ON " +
                    "prd.requisition_id = pr.id " +
                "WHERE " +
                    "ilm.inquiry_list_id = :id " +
                "AND " +
                    "prd.is_del = :isDel",
                PurchaseOrderDetailVO.class,
                new HashMap<String, Object>(){{
                    put("id", inquiryListId);
                    put("isDel", CommonUtils.IS_DEL_NO);
                }});
    }

    /**
     * 获取询比价采购订单详情
     * @param inquiryListId 询价单id
     * @return 采购订单详情
     */
    @Override
    public List<PurchaseOrderDetailVO> getOtherOrderDetail(Long inquiryListId) {
        return daoSupport.selectDataSql(
                "SELECT " +
                    "prd.id, " +
                    "qpd.supplier_id supplierId, " +
                    "pr.company_name companyName, " +
                    "pr.create_user_name linkman, " +
                    "pr.tel tel, " +
                    "pr.address, " +
                    "prd.material_id materialId, " +
                    "prd.material_code materialCode, " +
                    "prd.material_brand materialBrand, " +
                    "prd.material_name materialName, " +
                    "prd.material_model materialModel, " +
                    "prd.material_category materialCategory, " +
                    "prd.quantity, " +
                    "prd.material_unit materialUnit, " +
                    "qpd.price " +
                "FROM " +
                    "purchasing_requisition_detail prd " +
                "LEFT JOIN " +
                    "inquiry_list_material ilm " +
                "ON " +
                    "prd.inquiry_list_material_id = ilm.id " +
                "LEFT JOIN " +
                    "quoted_price_detail qpd " +
                "ON " +
                    "ilm.id = qpd.material_id " +
                "AND " +
                    "ilm.supplier_id = qpd.supplier_id " +
                "LEFT JOIN " +
                    "purchasing_requisition pr " +
                "ON " +
                    "prd.requisition_id = pr.id " +
                "WHERE " +
                    "ilm.inquiry_list_id = :id " +
                "AND " +
                    "prd.is_del = :isDel",
                PurchaseOrderDetailVO.class,
                new HashMap<String, Object>(){{
                    put("id", inquiryListId);
                    put("isDel", CommonUtils.IS_DEL_NO);
                }});
    }

    /**
     * 根据id集合获取对象集合
     * @param ids id集合
     * @return 对象集合
     */
    @Override
    public List<PurchasingRequisitionDetail> getByIds(Set<Long> ids) {
        return daoSupport.selectDataSql(
                "SELECT " +
                    "prd.* " +
                "FROM " +
                    "purchasing_requisition_detail prd " +
                "WHERE " +
                    "prd.id IN (:ids) " +
                "AND " +
                    "prd.is_del = :isDel",
                new HashMap<String, Class<?>>(){{
                    put("prd", PurchasingRequisitionDetail.class);
                }},
                new HashMap<String, Object>(){{
                    put("ids", ids);
                    put("isDel", CommonUtils.IS_DEL_NO);
                }});
    }

    /**
     * 根据采购单id获取物资集合
     * @param id 采购单id
     * @return 对象集合
     */
    @Override
    public List<SupplyMaterialDetailR> getDetailsByOrderId(Long id) {
//        return daoSupport.selectDataSql(
//            "SELECT " +
//                    "prd.* " +
//                    "FROM " +
//                    "purchasing_requisition_detail prd " +
//                    "WHERE " +
//                    "prd.purchase_order_id = :id " +
//                    "order by prd.id ",
//            new HashMap<String, Class<?>>(){{
//                put("prd", SupplyMaterialDetailR.class);
//            }},
//            new HashMap<String, Object>(){{
//                put("id", id);
//            }});
        String sql="SELECT * FROM(SELECT " +
                "prd.*,qpd.price AS mprice " +
                "FROM " +
                "purchasing_requisition_detail prd " +
                "LEFT JOIN " +
                "inquiry_list_material ilm " +
                "ON " +
                "prd.inquiry_list_material_id = ilm.id " +
                "LEFT JOIN " +
                "quoted_price_detail qpd " +
                "ON " +
                "qpd.material_id = ilm.id " +
                "WHERE  " +
                "prd.purchase_order_id = :oid " +
                "ORDER BY prd.id) d LEFT JOIN  " +
                "(SELECT smd.material_id AS smdid,SUM(num)AS msum FROM supply_material_detail smd " +
                "WHERE smd.supply_material_id=(SELECT id FROM supply_material WHERE purchase_order_id=:id) " +
                "GROUP BY smd.material_code) sd ON d.id=sd.smdid ";
        sql = sql + " order by id";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("id", id);
        args.put("oid", id);
        List<SupplyMaterialDetailR> result = daoSupport.selectDataSql(sql, SupplyMaterialDetailR.class,args);
        return result;
    }

}